package com.ms.orm.util;
import com.ms.common.util.*;
import com.ms.orm.annotation.DbType;
import com.ms.orm.conditions.AbstractWrapper;
import com.ms.orm.conditions.Wrapper;
import com.ms.orm.constants.Constants;
import com.ms.orm.constants.IdType;
import com.ms.orm.context.DefaultLittleOrmContext;
import com.ms.orm.exception.BaseSqlException;
import com.ms.orm.meta.EntityAndTableInfo;
import com.ms.orm.meta.IPage;
import com.ms.orm.meta.SqlAndParams;
import org.springframework.jdbc.core.SqlParameterValue;


import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * @author .
 */
public class SqlGenerator implements Constants{

    /**
     * 生成 INSERT 语句。
     */
    public static SqlAndParams generateInsertSql(Object bean)  {
        Class<?> clazz = bean.getClass();
        EntityAndTableInfo tableInfo = getTableInfo(clazz);
        String tableName = tableInfo.getTableName();
        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();
        List<Object> valueList = new ArrayList<>();
        //1、基于数据库自增注解，则过滤id自动
        if(tableInfo.getPrimaryKey()!=null && tableInfo.getPrimaryKey().getIdType().equals(IdType.AUTO)){
            //1、过滤id字段、过滤@TableField(exit = false）
            tableInfo.getProperties().stream().filter(p->(!p.isIdExit() && p.isFieldExit())).map(p->{
                columns.append(p.getColumnName()).append(", ");
                // 使用 ? 占位符，实际值由框架或 DAO 层填充
                values.append("?, ");
                //
                SqlParameterValue  parameterValue = new SqlParameterValue(p.getJdbcType(),ReflectUtils.invokeMethod(p.getPropertyReadMethod(),bean));
                valueList.add(parameterValue);
                return p;
            }).collect(Collectors.toList());;
        }else{
            //暂时忽略其他生成方式

            //1、过滤id字段、过滤@TableField(exit = false）
            tableInfo.getProperties().stream().filter(p->(p.isFieldExit())).map(p->{
                columns.append(p.getColumnName()).append(", ");
                // 使用 ? 占位符，实际值由框架或 DAO 层填充
                values.append("?, ");
                //
                SqlParameterValue  parameterValue = new SqlParameterValue(p.getJdbcType(),ReflectUtils.invokeMethod(p.getPropertyReadMethod(),bean));
                valueList.add(parameterValue);
                return p;
            }).collect(Collectors.toList());;
        }

        if (columns.length() > 0) {
            // 移除最后一个逗号和空格
            columns.setLength(columns.length() - 2);
            values.setLength(values.length() - 2);
        }

        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columns, values);
        SqlAndParams sqlAndParams = new SqlAndParams();
        sqlAndParams.setSql(sql);
        sqlAndParams.setParams(valueList);

        return sqlAndParams;
    }

    /**
     * 生成 UPDATE by Id 语句。
     */
    public static SqlAndParams generateUpdateSql(Object bean)  {
        Class<?> clazz = bean.getClass();
        EntityAndTableInfo tableInfo = getTableInfo(clazz);
        if(tableInfo.getPrimaryKey()==null){
            throw new BaseSqlException("Meta Primary key is exits");
        }
        String tableName = tableInfo.getTableName();
        StringBuilder updateColumns = new StringBuilder();
        List<Object> valueList = new ArrayList<>();

        //1、1）过滤id字段、过滤 2）@TableField(exit = false）3）过滤
        tableInfo.getProperties().stream().filter(p->(!p.isIdExit() && p.isFieldExit() && !"class".equals(p.getPropertyName()))).map(p->{
            Object value = ReflectUtils.invokeMethod(p.getPropertyReadMethod(),bean);
            if(value!=null){
                updateColumns.append(p.getColumnName()).append(" = ?, ");
                valueList.add(value);
            }
            return p;
        }).collect(Collectors.toList());

        // 移除最后一个逗号和空格
        if (updateColumns.length() > 0) {
            updateColumns.setLength(updateColumns.length() - 2);
        }

        //获取主键值
        Object pkValue = ReflectUtils.invokeMethod(tableInfo.getPrimaryKey().getPropertyReadMethod(),bean);
        if(LangUtil.isEmpty(pkValue)){
            throw new BaseSqlException("pkValue is null");
        }
        valueList.add(pkValue);
        //生成与编译 更新语句
        String sql = String.format("UPDATE %s SET %s WHERE %s = ?", tableName, updateColumns, tableInfo.getPrimaryKey().getColumnName());

        SqlAndParams sqlAndParams = new SqlAndParams();
        sqlAndParams.setSql(sql);
        sqlAndParams.setParams(valueList);

        return sqlAndParams;
    }


    /**
     * 删除 条件字段匹配值，忽略值 为null的字段
     * @param entityClass
     * @param conditionMap
     * @return
     */
    public static  SqlAndParams generateDeleteSqlByMap(Class entityClass, Map<String,Object> conditionMap){
        EntityAndTableInfo tableInfo = getTableInfo(entityClass);
        List<String> columnNames = new ArrayList<>();
        List<Object> columnValues = new ArrayList<>();
        tableInfo.getProperties().stream().filter(p->conditionMap.get(p.getColumnName())!=null && !"".equals(conditionMap.get(p.getColumnName()).toString())).map(k->{
            columnNames.add(k.getColumnName());
            columnValues.add(conditionMap.get(k.getColumnName()));
            return k;
        }).collect(Collectors.toList());

        StringBuilder sql = null;
        if(columnNames.size() > 0){
            sql= new StringBuilder("DELETE FROM ").append(tableInfo.getTableName()).append(" WHERE ");
            for (String columnName : columnNames) {
                sql.append(columnName).append(" = ? AND ");
            }
        }
        if(sql==null){
            //删除 操作必须有条件，才能执行
            throw new BaseSqlException("delete condition could not be null");
        }
        // 移除最后一个 AND 和 空格
        sql.setLength(sql.length() - 4);
        SqlAndParams sqlAndParams = new SqlAndParams();
        sqlAndParams.setSql(sql.toString());
        sqlAndParams.setParams(columnValues);
        return sqlAndParams;
    }


    /**
     * 删除 wrapper 内部指定条件，条件不能为null
     * @param entityClass
     * @param wrapper
     * @return
     */
    public static SqlAndParams generateDeleteSqlByUpdateWrapper(Class entityClass,Wrapper wrapper){
        AbstractWrapper abstractWrapper = (AbstractWrapper) wrapper;
        EntityAndTableInfo tableInfo = getTableInfo(entityClass);
        StringBuilder sql = new StringBuilder("DELETE FROM ").append(tableInfo.getTableName()).append(" WHERE ");
        String condition = wrapper.getExpression().getSqlSegment();
        if(!StringUtils.hasLength(condition)){
            //删除 操作必须有条件，才能执行
            throw new BaseSqlException("delete condition could not be null 1");
        }
        if(CollectionUtils.isEmpty(abstractWrapper.getParamNameValue())){
            //删除 操作必须有条件，才能执行
            throw new BaseSqlException("delete condition could not be null 2");
        }
        sql.append(condition);

        SqlAndParams sqlAndParams = new SqlAndParams();
        sqlAndParams.setSql(sql.toString());
        sqlAndParams.setParams(abstractWrapper.getParamNameValue());
        return sqlAndParams;

    }


    /**
     * 删除 指定 ids集合数组
     * @param entityClass
     * @param ids
     * @return
     */
    public static SqlAndParams generateDeleteSqlByIds(Class entityClass, Collection<?> ids){
        EntityAndTableInfo tableInfo = getTableInfo(entityClass);
        StringBuilder sql = new StringBuilder("DELETE FROM ").append(tableInfo.getTableName()).append(" WHERE ");
        if(tableInfo.getPrimaryKey()==null){
            throw new BaseSqlException("pKey is not exits");
        }
        if(CollectionUtils.isEmpty(ids)){
            //删除 操作必须有条件，才能执行
            throw new BaseSqlException("delete condition could not be null");
        }
        sql.append(tableInfo.getPrimaryKey().getColumnName()).append(" IN (");

        for (int i = 0; i < ids.size(); i++) {
            if(i==ids.size()-1){
                sql.append("?");
            }else{
                sql.append("?,");
            }
        }
        sql.append(")");

        SqlAndParams sqlAndParams = new SqlAndParams();
        sqlAndParams.setSql(sql.toString());
        sqlAndParams.setParams(ids.stream().collect(Collectors.toList()));
        return sqlAndParams;
    }


    /**
     *  根据 entity 中存在的值设置值，updateWrapper 内为条件
     * @param entityType
     * @param updateWrapper
     * @return
     */
    public static SqlAndParams generateUpdateSqlByBeanAndUpdateWrapper(Class<?> entityType, Object bean, Wrapper<?> updateWrapper) {
        EntityAndTableInfo tableInfo = getTableInfo(entityType);
        if(tableInfo.getPrimaryKey()==null){
            throw new BaseSqlException("Meta Primary key is not exits");
        }
        String tableName = tableInfo.getTableName();
        StringBuilder updateColumns = new StringBuilder();
        List<Object> valueList = new ArrayList<>();

        //1、1）过滤id字段、过滤 2）@TableField(exit = false）3）过滤
        tableInfo.getProperties().stream().filter(p->(!p.isIdExit() && p.isFieldExit() && !"class".equals(p.getPropertyName()))).map(p->{
            Object value = ReflectUtils.invokeMethod(p.getPropertyReadMethod(),bean);
            if(value!=null){
                updateColumns.append(p.getColumnName()).append(" = ?, ");
                valueList.add(value);
            }
            return p;
        }).collect(Collectors.toList());

        // 移除最后一个逗号和空格
        if (updateColumns.length() > 0) {
            updateColumns.setLength(updateColumns.length() - 2);
        }

        //1）获取主键值
        if(tableInfo.getPrimaryKey()!=null){
            Object pkValue = ReflectUtils.invokeMethod(tableInfo.getPrimaryKey().getPropertyReadMethod(),bean);
            if(pkValue!=null){
                //id存在，根据id更新
                valueList.add(pkValue);
                //生成与编译 更新语句
                String sql = String.format("UPDATE %s SET %s WHERE %s = ?", tableName, updateColumns, tableInfo.getPrimaryKey().getColumnName());
                SqlAndParams sqlAndParams = new SqlAndParams();
                sqlAndParams.setSql(sql);
                sqlAndParams.setParams(valueList);
                return sqlAndParams;
            }
        }

        //2）id值不存在，根据其他条件更新
        AbstractWrapper wrapper = (AbstractWrapper) updateWrapper;
        String condition = wrapper.getExpression().getSqlSegment();
        if(!StringUtils.hasLength(condition)){
            //更新 操作必须有条件，才能执行
            throw new BaseSqlException("UPDATE condition could not be null 1");
        }
        if(CollectionUtils.isEmpty(wrapper.getParamNameValue())){
            //删除 操作必须有条件，才能执行
            throw new BaseSqlException("UPDATE condition could not be null 2");
        }
        //生成与编译 更新语句
        String sql = String.format("UPDATE %s SET %s WHERE ", tableName, updateColumns) + condition;

        SqlAndParams sqlAndParams = new SqlAndParams();
        sqlAndParams.setSql(sql);
        //把设置的值 + 条件值
        valueList.addAll(wrapper.getParamNameValue());
        sqlAndParams.setParams(valueList);

        return sqlAndParams;
    }


    /**
     * 根据 指定 ids集合数组 查询list
     * @param entityClass
     * @param ids
     * @return
     */
    public static SqlAndParams generateQuerySqlByIds(Class entityClass, Collection<?> ids){
        EntityAndTableInfo tableInfo = getTableInfo(entityClass);
        StringBuilder sql = new StringBuilder("SELECT * FROM ").append(tableInfo.getTableName()).append(" WHERE ");
        if(tableInfo.getPrimaryKey()==null){
            throw new BaseSqlException("【generateQuerySqlByIds】Meta Primary key is not exits");
        }
        if(CollectionUtils.isEmpty(ids)){
            //删除 操作必须有条件，才能执行
            throw new BaseSqlException("【generateQuerySqlByIds】 ids condition could not be null");
        }
        sql.append(tableInfo.getPrimaryKey().getColumnName()).append(" IN (");

        for (int i = 0; i < ids.size(); i++) {
            if(i==ids.size()-1){
                sql.append("?");
            }else{
                sql.append("?,");
            }
        }
        sql.append(")");

        SqlAndParams sqlAndParams = new SqlAndParams();
        sqlAndParams.setSql(sql.toString());
        sqlAndParams.setParams(ids.stream().collect(Collectors.toList()));
        return sqlAndParams;
    }


    /**
     * 根据map中 字段查询，
     * @param entityClass
     * @param conditionMap
     * @return
     */
    public static SqlAndParams generateQuerySqlByMap(Class<?> entityClass, Map<String, Object> conditionMap) {
        EntityAndTableInfo tableInfo = getTableInfo(entityClass);
        List<String> conditionColumnNames = new ArrayList<>();
        List<Object> conditionColumnValues = new ArrayList<>();
        tableInfo.getProperties().stream().filter(p->conditionMap.get(p.getColumnName())!=null && !"".equals(conditionMap.get(p.getColumnName()).toString())).map(k->{
            conditionColumnNames.add(k.getColumnName());
            conditionColumnValues.add(conditionMap.get(k.getColumnName()));
            return k;
        }).collect(Collectors.toList());

        StringBuilder sql = null;
        if(conditionColumnNames.size() > 0){
            sql= new StringBuilder("SELECT * FROM ").append(tableInfo.getTableName());
            if(conditionColumnNames.size() > 0){
                sql.append(" WHERE ");

            }
            for (String columnName : conditionColumnNames) {
                sql.append(columnName).append(" = ? AND ");
            }
        }

        // 移除最后一个 AND 和 空格
        sql.setLength(sql.length() - 4);
        SqlAndParams sqlAndParams = new SqlAndParams();
        sqlAndParams.setSql(sql.toString());
        sqlAndParams.setParams(conditionColumnValues);
        return sqlAndParams;
    }


    /**
     * 根据 wrapper 查询记录条数
     * @param entityClass
     * @param wrapper
     * @return
     */
    public static SqlAndParams generateSelectCountSqlByWrapper(Class<?> entityClass, Wrapper<?> wrapper) {
        AbstractWrapper abstractWrapper = (AbstractWrapper) wrapper;
        EntityAndTableInfo tableInfo = getTableInfo(entityClass);
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM ").append(tableInfo.getTableName());
        String condition = wrapper.getExpression().getSqlSegment();
        if(StringUtils.hasLength(condition)){
            sql.append(" WHERE ").append(condition);
        }

        SqlAndParams sqlAndParams = new SqlAndParams();
        sqlAndParams.setSql(sql.toString());
        sqlAndParams.setParams(abstractWrapper.getParamNameValue());
        return sqlAndParams;
    }

    /**
     * 获取表名。
     */
    private static EntityAndTableInfo getTableInfo(Class<?> clazz) {
        return DefaultLittleOrmContext.getDefaultLittleOrmContext().getEntityAndTableInfo(clazz);
    }

    /**
     * 构建分页查询 语句
     * @param page
     * @param wrapper
     * @param <P>
     */
    public static <P extends IPage<?>> SqlAndParams generateQueryPageSqlByWrapper(P page, Wrapper<?> wrapper) {
        AbstractWrapper abstractWrapper = (AbstractWrapper) wrapper;
        EntityAndTableInfo tableInfo = getTableInfo(abstractWrapper.getEntityClass());

        //通常这些需要支持不同数据库，
        if(DefaultLittleOrmContext.dbType==null || DefaultLittleOrmContext.dbType.equals(DbType.MYSQL)){
            StringBuilder sql = new StringBuilder("SELECT * FROM ").append(tableInfo.getTableName());
            String condition = wrapper.getExpression().getSqlSegment();
            if(StringUtils.hasLength(condition)){
                sql.append(" WHERE ").append(condition);
            }
            //LIMIT 表示限制多少条记录
            //OFFSET 表示从哪个偏移位置开始
            sql.append(" LIMIT ? OFFSET ?");

            SqlAndParams sqlAndParams = new SqlAndParams();
            sqlAndParams.setSql(sql.toString());

            List paramList = abstractWrapper.getParamNameValue();
            paramList.add(page.getSize());
            paramList.add(page.offset());
            sqlAndParams.setParams(paramList);
            return sqlAndParams;
        }else if(DefaultLittleOrmContext.dbType!=null && DefaultLittleOrmContext.dbType.equals(DbType.ORACLE)){
            //StringBuilder sql = new StringBuilder("SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (SELECT * FROM ").append(tableInfo.getTableName());
            StringBuilder sql = new StringBuilder("SELECT * FROM (");
                sql.append("SELECT a.*, ROWNUM rnum FROM (");
                    sql.append(tableInfo.getTableName());
                    String condition = wrapper.getExpression().getSqlSegment();
                    if(StringUtils.hasLength(condition)){
                        sql.append(" WHERE ").append(condition);
                    }
                //这里是  upperLimit(最大行号数): page * pageSize + pageSize
                sql.append(") a ").append(" WHERE ROWNUM <= ?");
                //这里是  lowerLimit(最小行号数): 应该设置为 page * pageSize + 1
            sql.append(" ) WHERE rnum >= ?");


            SqlAndParams sqlAndParams = new SqlAndParams();
            sqlAndParams.setSql(sql.toString());
            List paramList = abstractWrapper.getParamNameValue();
            paramList.add(page.getCurrent()*page.getSize()+page.getSize());
            paramList.add(page.offset());
            sqlAndParams.setParams(paramList);
            return sqlAndParams;

        }else{
            throw new BaseSqlException("Not support db !");
        }
    }

    /**
     * 批量插入sql 与参数生成
     * @param entityType
     * @param list
     * @return
     */
    public static SqlAndParams generateBatchSaveSqlByList(Class<?> entityType, List<?> list) {
        EntityAndTableInfo tableInfo = getTableInfo(entityType);

        String tableName = tableInfo.getTableName();

        //1、列名
        StringBuilder columns = new StringBuilder();
        //2、列值占位符
        StringBuilder values = new StringBuilder();

        //1、基于数据库自增注解，则过滤id自动
        AtomicInteger index = new AtomicInteger(0);
        if(tableInfo.getPrimaryKey()!=null && tableInfo.getPrimaryKey().getIdType().equals(IdType.AUTO)){
            //1、过滤id字段、过滤@TableField(exit = false）
            tableInfo.getProperties().stream().filter(p->(!p.isIdExit() && p.isFieldExit())).map(p->{
                columns.append(p.getColumnName()).append(", ");
                // 使用 ? 占位符，实际值由框架或 DAO 层填充
                values.append("?, ");


                return p;
            }).collect(Collectors.toList());;
        }else{
            //暂时忽略其他生成方式
            //1、过滤id字段、过滤@TableField(exit = false）
            tableInfo.getProperties().stream().filter(p->(p.isFieldExit())).map(p->{
                columns.append(p.getColumnName()).append(", ");
                // 使用 ? 占位符，实际值由框架或 DAO 层填充
                values.append("?, ");
                return p;
            }).collect(Collectors.toList());;
        }

        if (columns.length() > 0) {
            // 移除最后一个逗号和空格
            columns.setLength(columns.length() - 2);
            values.setLength(values.length() - 2);
        }

        //3、参数列表
        List<Object[]> paramsList = new ArrayList<>();
        String[] columnNames =  columns.toString().split(COMMA);
        for (int j = 0; j < list.size(); j++) {
            Object[] params = new  Object[columnNames.length];
            for (int i = 0; i < columnNames.length; i++) {
                Object  parameterValue = MetaDataOperator.getProperValue(list.get(j),columnNames[i]);
                params[i] = parameterValue;
            }
            paramsList.add(params);
        }

        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columns, values);
        SqlAndParams sqlAndParams = new SqlAndParams();

        sqlAndParams.setSql(sql);
        sqlAndParams.setParams(paramsList);
        return sqlAndParams;

    }


    /**
     * oracle 分页查询语句
     *
     * SELECT *
     * FROM (
     *     SELECT a.*, ROWNUM rnum
     *     FROM (
     *         SELECT *
     *         FROM employees
     *         ORDER BY employee_id -- 根据需要调整排序字段
     *     ) a
     *     WHERE ROWNUM <= :upperLimit
     * )
     * WHERE rnum >= :lowerLimit;
     *
     */
}
